﻿<%@ WebHandler Language="C#" Class="ZSAssemWorkFlowHandler" %>

using System;
using System.Web;
using BusinessRule.DTO;
using System.Web.SessionState;
using DatabaseAccess.DBUtility;
using System.Data.OracleClient;
using System.Data;
using System.Linq;
using System.Collections.Generic;
using System.Collections;
using System.Text.RegularExpressions;
using Newtonsoft.Json;
using BusinessRule.Business;
using BusinessRule.IBusiness;
using BusinessRule.BusinessFactory;

public class ZSAssemWorkFlowHandler : IHttpHandler, IRequiresSessionState
{
    CurrentUserInfo m_UserInfo;
    private uLMSCommonIBusiness m_CommonObj = uMESBusinessFactory.instance.GetuLMSCommonObject();
    string fileBasePath = System.Configuration.ConfigurationManager.AppSettings["ImportPath"].ToString();//导入临时路径
    public void ProcessRequest(HttpContext context)
    {
        try
        {
            var action = context.Request["action"].ToString();
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            switch (action)
            {
                case"GetWorkCenter":
                    GetWorkCenter(context);
                    break;
                case "GetTeam":
                    GetTeam(context);
                    break;
                case"GetWorkStations":
                    GetWorkStations(context);
                    break;       
                case"GetWorkcenterAndTeam":
                    GetWorkcenterAndTeam(context);
                    break;  
                case"GetTemplate":
                    GetTemplate(context);
                    break;      
                case "GetAssemWorkFlowTemplate"://查询施工单模板信息
                    GetAssemWorkFlowTemplate(context);
                    break;
                case"SaveAssemWorkFlowTemplate":
                    SaveAssemWorkFlowTemplate(context);
                    break;
                case"DeleteAssemWorkFlowTemplate":
                    DeleteAssemWorkFlowTemplate(context);
                    break;    
                case "GetAssemWorkFlowSpec":
                    GetAssemWorkFlowSpec(context);
                    break;
                case"SaveAssemWorkFlowSpec":
                    SaveAssemWorkFlowSpec(context);
                    break;
                case"DeleteAssemWorkFlowSpec":
                    DeleteAssemWorkFlowSpec(context);
                    break;   
                case "GetAssemWorkFlowStep":
                    GetAssemWorkFlowStep(context);
                    break;  
                case"SaveAssemWorkFlowStep":
                    SaveAssemWorkFlowStep(context);
                    break;
                case"DeleteAssemWorkFlowStep":
                    DeleteAssemWorkFlowStep(context);
                    break;
                case "ImportAssemWorkFlowTemplate":
                    ImportAssemWorkFlowTemplate(context);
                    break;   
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            var result = new uMESProcessResult();
            result.Result = false;
            result.Message = ex.Message;
            context.Response.ContentType = "text/html";
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }
 
    public bool IsReusable {
        get {
            return false;
        }
    }

    private void GetTeam(HttpContext context) 
    {
        DataTable dtTeam=m_CommonObj.GetTeamByFactoryID(m_UserInfo.FactoryID);
        DataRow row = dtTeam.NewRow();
        row["TEAMID"] = "";
        row["TEAMNAME"] = "\u3000";
        dtTeam.Rows.Add(row);
        context.Response.Write(JsonConvert.SerializeObject(dtTeam));
    }
    private void GetWorkCenter(HttpContext context)
    {
        DataTable dtWorkcenter = m_CommonObj.GetWorkCenterByFacoryID(m_UserInfo.FactoryID);
        DataRow wRow = dtWorkcenter.NewRow();
        wRow["WORKCENTERID"] = "";
        wRow["WORKCENTERNAME"] = "\u3000";
        dtWorkcenter.Rows.Add(wRow);
        context.Response.Write(JsonConvert.SerializeObject(dtWorkcenter));
    }
    private void GetWorkStations(HttpContext context)
    {
        var strSQL = @"SELECT w.WORKSTATIONID,w.WORKSTATIONNAME FROM WORKSTATION w 
                WHERE w.FACTORYID ='" + m_UserInfo.FactoryID + "' ORDER BY  w.SHIFTID,w.WORKSTATIONNAME ";
        DataTable wsDT = DatabaseHelper.Query_ReturnDatTable(strSQL);
        DataRow wRow = wsDT.NewRow();
        wRow["WORKSTATIONID"] = "";
        wRow["WORKSTATIONNAME"] = "\u3000";
        wsDT.Rows.Add(wRow);
        context.Response.Write(JsonConvert.SerializeObject(wsDT));
    }
    

    private void GetWorkcenterAndTeam(HttpContext context) 
    {
        var result = new uMESProcessResult();
        DataTable dtTeam = m_CommonObj.GetTeamByFactoryID(m_UserInfo.FactoryID);
        DataRow row = dtTeam.NewRow();
        row["TEAMID"] = "";
        row["TEAMNAME"] = "\u3000";
        dtTeam.Rows.Add(row);
        DataTable dtWorkcenter = m_CommonObj.GetWorkCenterByFacoryID(m_UserInfo.FactoryID);
        DataRow wRow = dtWorkcenter.NewRow();
        wRow["WORKCENTERID"] = "";
        wRow["WORKCENTERNAME"] = "\u3000";
        dtWorkcenter.Rows.Add(wRow);
        result.DBTable = dtWorkcenter;
        result.ReturnData = dtTeam;
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    
    private void  GetTemplate(HttpContext context)
    {
        var Name = context.Request["Name"].ToString();
        var selectSQL = string.Format("select a.ASSEMWORKFLOWTEMPLATEID ID,a.TEMPLATENAME NAME FROM AssemWorkFlowTemplate a  WHERE a.FactoryID='{0}'", m_UserInfo.FactoryID);
        if (!string.IsNullOrWhiteSpace(Name)) {
            selectSQL += " and a.TEMPLATENAME like '%" + Name + "%'";
        }
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(selectSQL);
        DataRow tRow = dt.NewRow();
        tRow["ID"] = "";
        tRow["NAME"] = "请选择";
        dt.Rows.Add(tRow);
        var result = new uMESProcessResult();
        result.Result = true;
        result.DBTable = dt;
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    
    private void GetAssemWorkFlowTemplate(HttpContext context) 
    {
        var templateName = context.Request["templateName"].ToString();
        string pageSize = context.Request["pageSize"].ToString();
        string pageNumber = context.Request["pageNumber"].ToString();
        var selectSQL = @"SELECT a.ASSEMWORKFLOWTEMPLATEID,a.TEMPLATENAME ,e.FULLNAME CREATENAME,to_char(a.CREATEDATE,'yyyy-MM-dd hh24:mi:ss')CREATEDATE,
                    e2.FULLNAME UPDATENAME,to_char(a.UPDATEDATE,'yyyy-MM-dd hh24:mi:ss')UPDATEDATE,a.NOTE 
                    FROM AssemWorkFlowTemplate a 
                    LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID =a.CREATEEMPLOYEEID 
                    LEFT JOIN EMPLOYEE e2 ON e2.EMPLOYEEID =a.UPDATEEMPLOYEEID 
                    WHERE a.FactoryID='"+m_UserInfo.FactoryID+"'";
        if (!string.IsNullOrWhiteSpace(templateName)) {
            selectSQL += " and a.TEMPLATENAME like '%" + templateName + "%'";
        }
        selectSQL += "order by a.CREATEDATE desc,a.TEMPLATENAME ";
        var paraPage = new uMESPagingDataDTO();
        paraPage.CurrentPageIndex =Convert.ToInt32(pageNumber);
        paraPage.PageSize = Convert.ToInt32(pageSize);
        paraPage.strSQL = selectSQL;
       var result = DatabaseHelper.RunProcedureForPage(paraPage);
        context.Response.Write(JsonConvert.SerializeObject(result)); 
    }

    private void SaveAssemWorkFlowTemplate(HttpContext context) 
    {
        var argDt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var insertSQL = @"insert into AssemWorkFlowTemplate(ASSEMWORKFLOWTEMPLATEID,TEMPLATENAME,CREATEEMPLOYEEID,CREATEDATE,NOTE,FactoryID)VALUES('{0}','{1}','{2}',sysdate,'{3}','{4}')";
        var updateSQL = @"update AssemWorkFlowTemplate set TEMPLATENAME='{0}',UPDATEEMPLOYEEID='{1}',UPDATEDATE=sysdate,NOTE='{2}' where ASSEMWORKFLOWTEMPLATEID='{3}'";
        var result = new uMESProcessResult();
        var arrSQL = new ArrayList();
        foreach(DataRow row in argDt.Rows)
        {
            if (string.IsNullOrWhiteSpace(row["TEMPLATENAME"].ToString())) continue;
            if (!string.IsNullOrWhiteSpace(row["ASSEMWORKFLOWTEMPLATEID"].ToString())) {
                arrSQL.Add(string.Format(updateSQL,row["TEMPLATENAME"],m_UserInfo.EmployeeID,row["NOTE"],row["ASSEMWORKFLOWTEMPLATEID"]));
            } else {
                arrSQL.Add(string.Format(insertSQL, Guid.NewGuid().ToString(), row["TEMPLATENAME"], m_UserInfo.EmployeeID, row["NOTE"],m_UserInfo.FactoryID));
            }
        }
        if (arrSQL.Count > 0)
        {
            result.Result = DatabaseHelper.ExecuteSqlListTran(arrSQL);
            result.Message = result.Result ? "保存成功" : "保存失败";
        }
        else {
            result.Message = "无修改数据需要保存";
        }
        context.Response.Write(JsonConvert.SerializeObject(result)); 
    }

    private void DeleteAssemWorkFlowTemplate(HttpContext context)
    {
        var ids = context.Request["ids"].ToString();
        var result = new uMESProcessResult();
        var arrSQL = new ArrayList();
        arrSQL.Add(string.Format("delete from AssemWorkFlowTemplateStep where ASSEMWORKFLOWTEMPLATESPECID in (select ASSEMWORKFLOWTEMPLATESPECID from AssemWorkFlowTemplateSpec where ASSEMWORKFLOWTEMPLATEID in ('{0}'))", ids.Replace(",", "','")));
        arrSQL.Add(string.Format("delete from AssemWorkFlowTemplateSpec where ASSEMWORKFLOWTEMPLATEID in ('{0}')", ids.Replace(",", "','")));
        arrSQL.Add(string.Format("delete from AssemWorkFlowTemplate where ASSEMWORKFLOWTEMPLATEID in ('{0}')", ids.Replace(",", "','")));
        result.Result = DatabaseHelper.ExecuteSqlListTran(arrSQL);
        result.Message = result.Result ? "删除成功" : "删除失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    private void GetAssemWorkFlowSpec(HttpContext context)
    {
        var argTemplateID = context.Request["tid"].ToString();
        var dt = DatabaseHelper.Query_ReturnDatTable(string.Format(@"select w.workcentername,t.teamname,null WORKSTATIONNAME,
                    sp.* from AssemWorkFlowTemplateSpec sp 
                    left join workcenter w on w.workcenterid=sp.workcenterid 
                    left join team t on t.teamid=sp.teamid                   
                    where ASSEMWORKFLOWTEMPLATEID='{0}' order by to_number(SPECNO) ASC", argTemplateID));
        foreach (DataRow row in dt.Rows)
        {
            DataTable dtt = DatabaseHelper.Query_ReturnDatTable(string.Format("SELECT wm_concat(wk.WORKSTATIONNAME) WORKSTATIONNAME FROM workstation wk WHERE wk.WORKSTATIONID IN ('{0}')", row["WORKSTATIONIDS"].ToString().Replace(",", "','")));
            if (dtt.Rows.Count > 0)
            {
                row["WORKSTATIONNAME"] = dtt.Rows[0]["WORKSTATIONNAME"].ToString();
            }
        }
        context.Response.Write(JsonConvert.SerializeObject(dt)); 
    }
    
    private void SaveAssemWorkFlowSpec(HttpContext context) 
    {
        var argDt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var argTemplateID = context.Request["tid"].ToString();
        var insertSQL = @"insert into AssemWorkFlowTemplateSpec(ASSEMWORKFLOWTEMPLATESPECID,ASSEMWORKFLOWTEMPLATEID,SPECNO,WORKCENTERID,TEAMID,SPECNAME,SPECDESC,WORKSTATIONIDS,PREPARECYCLE,MAKECYCLE,TRANSFERCYCLE,CHANGEOVERCYCLE)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')";
        var updateSQL = @"update AssemWorkFlowTemplateSpec set SPECNO='{0}',WORKCENTERID='{1}',TEAMID='{2}',SPECNAME='{3}',SPECDESC='{4}',WORKSTATIONIDS='{5}',PREPARECYCLE='{6}',MAKECYCLE='{7}',TRANSFERCYCLE='{8}',CHANGEOVERCYCLE='{9}' where ASSEMWORKFLOWTEMPLATESPECID='{10}'";
        var result = new uMESProcessResult();
        var arrSQL = new ArrayList();
        foreach (DataRow row in argDt.Rows)
        {
            if (string.IsNullOrWhiteSpace(row["SPECNAME"].ToString())) continue;
            if (!string.IsNullOrWhiteSpace(row["ASSEMWORKFLOWTEMPLATESPECID"].ToString()))
            {
                arrSQL.Add(string.Format(updateSQL, row["SPECNO"], row["WORKCENTERID"], row["TEAMID"], row["SPECNAME"], row["SPECDESC"], row["WORKSTATIONIDS"], string.IsNullOrWhiteSpace(row["PREPARECYCLE"].ToString()) ? "0" : row["PREPARECYCLE"], string.IsNullOrWhiteSpace(row["MAKECYCLE"].ToString()) ? "0" : row["MAKECYCLE"], string.IsNullOrWhiteSpace(row["TRANSFERCYCLE"].ToString()) ? "0" : row["TRANSFERCYCLE"], string.IsNullOrWhiteSpace(row["CHANGEOVERCYCLE"].ToString()) ? "0" : row["CHANGEOVERCYCLE"], row["ASSEMWORKFLOWTEMPLATESPECID"]));
            }
            else
            {
                arrSQL.Add(string.Format(insertSQL, Guid.NewGuid().ToString(), argTemplateID, row["SPECNO"], row["WORKCENTERID"], row["TEAMID"], row["SPECNAME"], row["SPECDESC"], row["WORKSTATIONIDS"], string.IsNullOrWhiteSpace(row["PREPARECYCLE"].ToString()) ? "0" : row["PREPARECYCLE"], string.IsNullOrWhiteSpace(row["MAKECYCLE"].ToString()) ? "0" : row["MAKECYCLE"], string.IsNullOrWhiteSpace(row["TRANSFERCYCLE"].ToString()) ? "0" : row["TRANSFERCYCLE"], string.IsNullOrWhiteSpace(row["CHANGEOVERCYCLE"].ToString()) ? "0" : row["CHANGEOVERCYCLE"]));
            }
        }
        if (arrSQL.Count > 0)
        {
            result.Result = DatabaseHelper.ExecuteSqlListTran(arrSQL);
            result.Message = result.Result ? "保存成功" : "保存失败";
        }
        else
        {
            result.Message = "无修改数据需要保存";
        }
        context.Response.Write(JsonConvert.SerializeObject(result)); 
    }
    /// <summary>
    /// 工序删除
    /// </summary>
    /// <param name="context"></param>
    private void DeleteAssemWorkFlowSpec(HttpContext context)
    {
        var ids = context.Request["ids"].ToString();
        var result = new uMESProcessResult();
        var arrSQL = new ArrayList();
        arrSQL.Add(string.Format("delete from AssemWorkFlowTemplateStep where ASSEMWORKFLOWTEMPLATESPECID in ('{0}')", ids.Replace(",", "','")));
        arrSQL.Add(string.Format("delete from AssemWorkFlowTemplateSpec where ASSEMWORKFLOWTEMPLATESPECID in ('{0}')", ids.Replace(",", "','")));
        result.Result = DatabaseHelper.ExecuteSqlListTran(arrSQL);
        result.Message = result.Result ? "删除成功" : "删除失败";
        if (result.Result)
        {
            var tid = context.Request["tid"].ToString();
            var selectSQL = @"select st.* from AssemWorkFlowTemplateSpec st where st.ASSEMWORKFLOWTEMPLATEID='" + tid + "'";
            var arrSQLL = new ArrayList();
            var dt = DatabaseHelper.Query_ReturnDatTable(selectSQL);
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                arrSQLL.Add(string.Format("update AssemWorkFlowTemplateSpec set specno='{0}' where ASSEMWORKFLOWTEMPLATESPECID='{1}'", i + 1, dt.Rows[i]["ASSEMWORKFLOWTEMPLATESPECID"]));
            }
            if (arrSQLL.Count > 0)
            {
                DatabaseHelper.ExecuteSqlListTran(arrSQLL);
            }
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    
    /// <summary>
    /// 工步查询
    /// </summary>
    /// <param name="context"></param>
    private void GetAssemWorkFlowStep(HttpContext context)
    {
        var argTemplateID = context.Request["tid"].ToString();
        var specid = context.Request["specid"].ToString();
        var selectSQL = "select sp.SPECNO,st.* from AssemWorkFlowTemplateStep st left join AssemWorkFlowTemplateSpec sp on sp.ASSEMWORKFLOWTEMPLATESPECID=st.ASSEMWORKFLOWTEMPLATESPECID where 1=1";
        if (!string.IsNullOrWhiteSpace(argTemplateID)) {
            selectSQL += " and st.ASSEMWORKFLOWTEMPLATESPECID IN ( select ASSEMWORKFLOWTEMPLATESPECID from AssemWorkFlowTemplateSpec where ASSEMWORKFLOWTEMPLATEID='" + argTemplateID + "')";
        }
        if (!string.IsNullOrWhiteSpace(specid)) {
            selectSQL += " and st.ASSEMWORKFLOWTEMPLATESPECID='" + specid + "'";
        }
        selectSQL += " order by sp.SPECNO,st.STEPNO ASC";
        var dt = DatabaseHelper.Query_ReturnDatTable(selectSQL);
        context.Response.Write(JsonConvert.SerializeObject(dt));
    }
    /// <summary>
    /// 工步保存
    /// </summary>
    /// <param name="context"></param>
    private void SaveAssemWorkFlowStep(HttpContext context)
    {
        var argDt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var specid = context.Request["specid"].ToString();
        var insertSQL = @"insert into AssemWorkFlowTemplateStep(ASSEMWORKFLOWTEMPLATESTEPID,ASSEMWORKFLOWTEMPLATESPECID,STEPNO,STEPNAME)VALUES('{0}','{1}','{2}','{3}')";
        var updateSQL = @"update AssemWorkFlowTemplateStep set STEPNO='{0}',STEPNAME='{1}' where ASSEMWORKFLOWTEMPLATESTEPID='{2}'";
        var result = new uMESProcessResult();
        var arrSQL = new ArrayList();
        foreach (DataRow row in argDt.Rows)
        {
            if (string.IsNullOrWhiteSpace(row["STEPNAME"].ToString())) continue;
            if (!string.IsNullOrWhiteSpace(row["ASSEMWORKFLOWTEMPLATESTEPID"].ToString()))
            {
                arrSQL.Add(string.Format(updateSQL, row["STEPNO"], row["STEPNAME"], row["ASSEMWORKFLOWTEMPLATESTEPID"]));
            }
            else
            {
                arrSQL.Add(string.Format(insertSQL, Guid.NewGuid().ToString(), specid, row["STEPNO"], row["STEPNAME"]));
            }
        }
        if (arrSQL.Count > 0)
        {
            result.Result = DatabaseHelper.ExecuteSqlListTran(arrSQL);
            result.Message = result.Result ? "保存成功" : "保存失败";
        }
        else
        {
            result.Message = "无修改数据需要保存";
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 工步删除
    /// </summary>
    /// <param name="context"></param>
    private void DeleteAssemWorkFlowStep(HttpContext context) 
    {
        var ids = context.Request["ids"].ToString();
        var result = new uMESProcessResult();
        result.Result = DatabaseHelper.ExecuteSql(string.Format("delete from AssemWorkFlowTemplateStep where ASSEMWORKFLOWTEMPLATESTEPID in ('{0}')", ids.Replace(",","','"))) > 0;
        result.Message = result.Result ? "删除成功" : "删除失败";
        if(result.Result){
            var specid = context.Request["specid"].ToString();
            var selectSQL = @"select st.* from AssemWorkFlowTemplateStep st where st.ASSEMWORKFLOWTEMPLATESPECID='" + specid + "'";
            var arrSQL = new ArrayList();
            var dt=DatabaseHelper.Query_ReturnDatTable(selectSQL);
            for (var i = 0; i < dt.Rows.Count;i++ )
            {
                arrSQL.Add(string.Format("update AssemWorkFlowTemplateStep set stepno='{0}' where ASSEMWORKFLOWTEMPLATESTEPID='{1}'", i+1,dt.Rows[i]["ASSEMWORKFLOWTEMPLATESTEPID"]));
            }
            if (arrSQL.Count>0) {
                DatabaseHelper.ExecuteSqlListTran(arrSQL);
            }
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 模板导入
    /// </summary>
    /// <param name="context"></param>
    private void ImportAssemWorkFlowTemplate(HttpContext context) 
    {
        string fpath = string.Empty;
        try
        {
            var files = context.Request.Files;
            if (files.Count == 0)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "未上传文件！", Result = false }));
                return;
            }
            if (!System.IO.Directory.Exists(context.Server.MapPath(fileBasePath)))
            {
                System.IO.Directory.CreateDirectory(context.Server.MapPath(fileBasePath));
            }
            var file = files.Count > 1 ? files[files.Count - 1] : files[0];
            string IsXls = System.IO.Path.GetExtension(file.FileName);
            string fileName = System.IO.Path.GetFileNameWithoutExtension(file.FileName) + DateTime.Now.ToString("yyyyMMddHHmmssffff") + IsXls;
            fpath = context.Server.MapPath(fileBasePath + fileName);
            file.SaveAs(fpath);//保存文件，处理完成后删除
            NPOI.SS.UserModel.IWorkbook Workbook = uLMS_CommonFunction.GetWorkbookObj(fpath);
            DataTable dt = uLMS_CommonFunction.ExcelImportToDataTable(Workbook);
            DataTable dt1 = uLMS_CommonFunction.ExcelSheetImportToDataTable(Workbook,1);
            DataTable dt2 = uLMS_CommonFunction.ExcelSheetImportToDataTable(Workbook,2);
            uLMS_CommonFunction.DeleteFile(fpath);
            uMESProcessResult result = SaveImportAssemWorkFlowTemplate(dt, dt1, dt2);
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
        catch (Exception ex)
        {
            uLMS_CommonFunction.DeleteFile(fpath);//报错，则删除上传的文件
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = ex.Message, Result = false }));
        }
    }
    private uMESProcessResult SaveImportAssemWorkFlowTemplate(DataTable dt, DataTable dt1, DataTable dt2) 
    {
        var result = new uMESProcessResult();
        var insertSQL = @"insert into AssemWorkFlowTemplate(ASSEMWORKFLOWTEMPLATEID,TEMPLATENAME,CREATEEMPLOYEEID,CREATEDATE,NOTE,FactoryID)VALUES('{0}','{1}','{2}',sysdate,'{3}','{4}')";
        var insertSPEC = @"insert into AssemWorkFlowTemplateSpec(ASSEMWORKFLOWTEMPLATESPECID,ASSEMWORKFLOWTEMPLATEID,SPECNO,WORKCENTERID,TEAMID,SPECNAME,SPECDESC,WORKSTATIONIDS,PREPARECYCLE,MAKECYCLE,TRANSFERCYCLE,CHANGEOVERCYCLE)VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')";
        var insertSTEP = @"insert into AssemWorkFlowTemplateStep(ASSEMWORKFLOWTEMPLATESTEPID,ASSEMWORKFLOWTEMPLATESPECID,STEPNO,STEPNAME)VALUES('{0}','{1}','{2}','{3}')";
        var arrSQL = new ArrayList();
        DataTable dtTeam = m_CommonObj.GetTeamByFactoryID(m_UserInfo.FactoryID);
        DataTable dtWorkcenter = m_CommonObj.GetWorkCenterByFacoryID(m_UserInfo.FactoryID);
        foreach(DataRow row in dt.Rows)
        {
            var templateID = Guid.NewGuid().ToString();
            arrSQL.Add(string.Format(insertSQL, templateID, row["模板名称"],m_UserInfo.EmployeeID,row["备注"],m_UserInfo.FactoryID));
            DataRow[] dt1Row = dt1.Select("模板名称='" + row["模板名称"] + "'");
            if (dt1Row.Length>0) {
                for (var i = 0; i < dt1Row.Length; i++)
                {
                    var specID=Guid.NewGuid().ToString();
                    DataRow[] teamRow = dtTeam.Select("teamname='" + dt1Row[i]["组"] + "'");
                    DataRow[] wRow = dtWorkcenter.Select("workcentername='" + dt1Row[i]["工区"] + "'");
                    var teamid = teamRow.Length > 0 ? teamRow[0]["teamid"] : string.Empty;
                    var workcenterid = wRow.Length > 0 ? wRow[0]["workcenterid"].ToString() : string.Empty;
                    if (!string.IsNullOrWhiteSpace(workcenterid)) { teamid = string.Empty; }//导入有工区则忽略组
                    DataTable wkDT = DatabaseHelper.Query_ReturnDatTable(string.Format(@"SELECT wm_concat(wk.WORKSTATIONID) WORKSTATIONIDS FROM workstation wk WHERE wk.WORKSTATIONNAME IN ('{0}')", dt1Row[i]["工位"].ToString().Replace(",","','")));

                    arrSQL.Add(string.Format(insertSPEC, specID, templateID, i + 1, workcenterid, teamid, dt1Row[i]["工序"], dt1Row[i]["工序描述"], wkDT.Rows.Count > 0 ? wkDT.Rows[0]["WORKSTATIONIDS"] : null, dt1Row[i]["准备时间"], dt1Row[i]["加工时间"], dt1Row[i]["运转时间"], dt1Row[i]["换型时间"]));
                    
                    DataRow[] dt2Row = dt2.Select("模板名称='" + row["模板名称"] + "' and 工序='" + dt1Row[i]["工序"] + "'");
                    if (dt2Row.Length > 0)
                    {
                        for (var j = 0; j < dt2Row.Length; j++)
                        {
                            arrSQL.Add(string.Format(insertSTEP, Guid.NewGuid().ToString(), specID, j + 1, dt2Row[j]["工步描述"]));
                        }
                    }
                }
            }
        }
        if (arrSQL.Count > 0) {
            result.Result = DatabaseHelper.ExecuteSqlListTran(arrSQL);
            result.Message = result.Result ? "导入成功" : "导入失败";
            
        } else { result.Message = "导入模板数据为空"; }
        
        return result;
    }

}